package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Page;
import com.xxxy.jsq.model.Term;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TermDao implements BaseDao<Term> {
    private C3P0Utils db = new C3P0Utils();

    public TermDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Term model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("INSERT INTO pa_term(term_name, term_flag, term_order, term_stmp) ");
        sqlStr.append("VALUES(?, ?, ?, ?) ");

        conn = db.getConnection();
        // 检查学期名称是否重复
        boolean flag = isSame(model, true);
        if (!flag) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getTermName());
                pstm.setString(2, model.getTermFlag());
                pstm.setInt(3, model.getTermOrder());
                pstm.setTimestamp(4, model.getTermStmp());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return num > 0 ? "Ok" : "Err";
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Term model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        // 检查学期名称是否重复（排除自身）
        if (isSame(model, false)) {
            return "Same";
        }

        sqlStr.append("UPDATE pa_term SET ");
        if (model.getTermName() != null && !model.getTermName().equals("")) {
            sqlStr.append("term_name='" + model.getTermName() + "'");
        }
        if (model.getTermFlag() != null && !model.getTermFlag().equals("")) {
            sqlStr.append(", term_flag='" + model.getTermFlag() + "'");
        }
        if (model.getTermOrder() != null) {
            sqlStr.append(", term_order=" + model.getTermOrder());
        }
        if (model.getTermStmp() != null) {
            sqlStr.append(", term_stmp='" + model.getTermStmp() + "'");
        }
        sqlStr.append(" WHERE term_id=?");

        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getTermId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public String delete(Term model) {
        return deleteById(model.getTermId());
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("DELETE FROM pa_term WHERE term_id=? ");

        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public List<Term> getAllData(Term model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Term> list = new ArrayList<Term>();

        sqlStr.append("SELECT * FROM pa_term ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getTermName() != null && !model.getTermName().equals("")) {
            sqlStr.append("AND term_name='" + model.getTermName() + "' ");
        }
        if (model.getTermFlag() != null && !model.getTermFlag().equals("")) {
            sqlStr.append("AND term_flag='" + model.getTermFlag() + "' ");
        }
        sqlStr.append("ORDER BY term_order ASC, term_name ASC ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Term term = new Term();
                term.setTermId(rs.getLong("term_id"));
                term.setTermName(rs.getString("term_name"));
                term.setTermFlag(rs.getString("term_flag").equals("0") ? "当前" : "非当前");
                term.setTermOrder(rs.getInt("term_order"));
                term.setTermStmp(rs.getTimestamp("term_stmp"));
                list.add(term);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Term findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT * FROM pa_term ");
        sqlStr.append("WHERE 1=1 AND term_id=? ");

        conn = db.getConnection();
        Term term = new Term();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                term.setTermId(rs.getLong("term_id"));
                term.setTermName(rs.getString("term_name"));
                term.setTermFlag(rs.getString("term_flag").equals("0") ? "当前" : "非当前");
                term.setTermOrder(rs.getInt("term_order"));
                term.setTermStmp(rs.getTimestamp("term_stmp"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return term;
    }

    @Override
    public Term find(Term model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT * FROM pa_term ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getTermName() != null && !model.getTermName().equals("")) {
            sqlStr.append("AND term_name='" + model.getTermName() + "' ");
        }
        if (model.getTermFlag() != null && !model.getTermFlag().equals("")) {
            sqlStr.append("AND term_flag='" + model.getTermFlag() + "' ");
        }

        conn = db.getConnection();
        Term term = new Term();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                term.setTermId(rs.getLong("term_id"));
                term.setTermName(rs.getString("term_name"));
                term.setTermFlag(rs.getString("term_flag").equals("0") ? "当前" : "非当前");
                term.setTermOrder(rs.getInt("term_order"));
                term.setTermStmp(rs.getTimestamp("term_stmp"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return term;
    }

    @Override
    public boolean isSame(Term model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT * FROM pa_term WHERE term_name=? ");
        // 更新时排除自身ID
        if (!sflag) {
            sqlStr.append("AND term_id!='" + model.getTermId() + "' ");
        }

        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getTermName());
            rs = pstm.executeQuery();
            flag = rs.next(); // 存在同名学期则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Term> getAllDataByPage(Term model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Term> list = new ArrayList<Term>();

        sqlStr.append("SELECT * FROM pa_term ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getTermName() != null && !model.getTermName().equals("")) {
            sqlStr.append("AND term_name LIKE '%" + model.getTermName() + "%' ");
        }
        if (model.getTermFlag() != null && !model.getTermFlag().equals("")) {
            sqlStr.append("AND term_flag='" + model.getTermFlag() + "' ");
        }
        sqlStr.append("ORDER BY term_order ASC, term_name ASC ");
        sqlStr.append("LIMIT ?, ? ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Term term = new Term();
                term.setTermId(rs.getLong("term_id"));
                term.setTermName(rs.getString("term_name"));
                term.setTermFlag(rs.getString("term_flag").equals("0") ? "当前" : "非当前");
                term.setTermOrder(rs.getInt("term_order"));
                term.setTermStmp(rs.getTimestamp("term_stmp"));
                list.add(term);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Term model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT COUNT(*) FROM pa_term ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getTermName() != null && !model.getTermName().equals("")) {
            sqlStr.append("AND term_name='" + model.getTermName() + "' ");
        }
        if (model.getTermFlag() != null && !model.getTermFlag().equals("")) {
            sqlStr.append("AND term_flag='" + model.getTermFlag() + "' ");
        }

        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Term> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Term> list = new ArrayList<Term>();

        sqlStr.append("SELECT * FROM pa_term WHERE term_flag='0' "); // 0表示当前学期
        sqlStr.append("ORDER BY term_order ASC ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Term term = new Term();
                term.setTermId(rs.getLong("term_id"));
                term.setTermName(rs.getString("term_name"));
                list.add(term);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}